﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Text.RegularExpressions;
using Conysoft;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
   //07301061
   //董敬超
   // 2010-6-4

namespace VideoWorld.DataAccess
{
    /// <summary>
    /// 返回用户信息
    /// </summary>
    /// 
    public class UserInfo
    {
        public String name;       //用户名
        public String passWord;   //用户密码
        public String email;      //电子邮件
    }

    public class Users
    {
        private String m_connectionString;
        private SQLTools m_SQLTools;
        public String errorString; //错误信息
        public Users(String connectionString)
        {
            m_connectionString = connectionString;
            m_SQLTools = new SQLTools(m_connectionString);
        }

        /// <summary>
        /// 测试该用户是否已注册
        /// </summary>
        public bool IsUserExist(String name)
        {
            String sql = String.Format("SELECT id FROM users WHERE name='{0}'", name);
            if (m_SQLTools.Int32FromSQL(sql, 0) == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 新用户注册
        /// </summary>
        /// <returns>成功返回true，否则false</returns>
        public bool Register(
            String name,
            String password,
            int status,
            DateTime timeOut,
            int score,
            int permission)
        {
            String sql = String.Format("EXECUTE registUser ?, ?, ?, ?, ?, ?");
            OleDbConnection conn = new OleDbConnection(m_connectionString);
            try
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.Parameters.Add("@name", OleDbType.Char, 64).Value = name;
                cmd.Parameters.Add("@password", OleDbType.Char, 32).Value = password;
                cmd.Parameters.Add("@status", OleDbType.Integer).Value = status;
                cmd.Parameters.Add("@timeOut", OleDbType.Date).Value = timeOut;
                cmd.Parameters.Add("@score", OleDbType.Integer).Value = score;
                cmd.Parameters.Add("@permission", OleDbType.Integer).Value = permission;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
#if DEBUG
                throw e;
#else
				return false;
				errorString = e.ToString() ;
#endif
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 更新用户资料
        /// </summary>
        /// <returns>成功返回true，否则false</returns>
        public bool UpdataUserInfo(
            int userId,
            String nickName,
            String email,
            String realName,
            int gender,
            String birthDay,
            String mobile,
            String country,
            String province,
            String city,
            int acceptEmail,
            int acceptNote,
            int publishEmail,
            DateTime regTime,
            String remark)
        {
            String sql = String.Format("SELECT id FROM users WHERE id={0}", userId);
            if (m_SQLTools.Int32FromSQL(sql, 0) != 0)
            {
                sql = String.Format("UPDATE userInfo SET nickName = '{0}',email = '{1}', realName = '{2}',gender = '{3}',birthDay = '{4}',mobile = '{5}',country = '{6}',province = '{7}',city = '{8}',acceptEmail = {9},acceptNote = {10},publishEmail = {11},regTime = '{12}',remark = '{13}' WHERE userID={14}", nickName, email, realName, gender, birthDay, mobile, country, province, city, acceptEmail, acceptNote, publishEmail, regTime, remark, userId);
                if (m_SQLTools.ExecuteNoQuery(sql))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 注销用户
        /// </summary>
        /// <returns>成功返回true，否则false</returns>
        public bool UnRegister(String name, String password)
        {
            String sql = String.Format("UPDATE users SET status=0 WHERE name='{0}' and password='{1}'", name, password);
            return m_SQLTools.ExecuteNoQuery(sql);
        }

        /// <summary>
        /// 返回该用户的ID号
        /// </summary>
        public int GetUserId(String name)
        {
            String sql = String.Format("SELECT id FROM users WHERE name='{0}'", name);
            return m_SQLTools.Int32FromSQL(sql, 0);
        }

        /// <summary>
        /// 返回该用户的帐号
        /// </summary>
        public String GetUserName(int userId)
        {
            String sql = String.Format("SELECT name FROM users WHERE Id={0}", userId);
            return m_SQLTools.StringFromSQL(sql);
        }

        /// <summary>
        /// 返回该用户的密码
        /// </summary>
        public String GetUserPassword(String name)
        {
            String sql = String.Format("SELECT password FROM users WHERE name='{0}'", name);
            return m_SQLTools.StringFromSQL(sql);
        }
        /// <summary>
        /// 检查当前用户是否已经充值,卡是否在使用中
        /// </summary>
        public int GetCheckCharge(int userID)
        {
            SqlParameter[] sparam = new SqlParameter[2];
            sparam[0] = new SqlParameter("@UserID", userID);
            sparam[1] = new SqlParameter("@Result", SqlDbType.Int, 4);
            sparam[1].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings["ConnectionString_Pay"].ToString(), CommandType.StoredProcedure, "CheckCharge", sparam);
            return Convert.ToInt32(sparam[1].Value);
        }
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <returns></returns>
        public String IsUserLogin(String name, String password)
        {
            String sql = String.Empty;
            String result = String.Empty;

            sql = String.Format("SELECT id FROM users WHERE name='{0}'", name);
            result = m_SQLTools.StringFromSQL(sql);
            if (result == null)
            {
                return "该用户不存在!";
            }

            sql = String.Format("SELECT id FROM users WHERE name='{0}' AND password='{1}'",
                name, password);
            result = m_SQLTools.StringFromSQL(sql);
            if (result == null)
            {
                return "你输入的密码不正确!";
            }

            sql = String.Format("SELECT TOP 1 userId FROM userBalance WHERE timeOut>'{0}' and userId=(Select ID FROM users WHERE name='{1}')", DateTime.Now, name);
            result = m_SQLTools.StringFromSQL(sql);
            if (result == null)
            {
                return "您的充值卡已经过期,或者没有购买充值，请购买新的充值卡进行充值!";
            }
            return "ok";
        }



        /// <summary>
        /// 登录信息LOG
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="ip">客户端IP地址</param>
        /// <returns>成功返回true,失败返回false</returns>
        public bool LogLogin(int userId, String ip)
        {
            String sql = String.Format("INSERT INTO loginLog(userId, ip, timeStamp) VALUES({0},'{1}','{2}')",
                userId, ip, DateTime.Now);

            return m_SQLTools.ExecuteNoQuery(sql);
        }

        public UserInfo GetUserInfo()
        {
            UserInfo ui = new UserInfo();
            ui.name = "BeiJing";
            return ui;
        }
    }
}
